package demo1;

import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @description: 通过PreparedStatement实现批量插入
 * （update/delete默认具备批量）
 * @author: apple
 *
 * CREATE TABLE goods(
 *
 * id INT PRIMARY KEY AUTO_INCREMENT,
 * NAME VARCHAR(25)
 *
 * );
 * 1、向表中插入2万条数据
 * @date:  2022/1/10 12:26 上午
 * @version 1.0
 **/
public class InsertBatchTest {

    // 批量插入数据：单条的
    @Test
    public void test1() throws SQLException, IOException {
        final Connection conn = JDBCUtil.getConnection();
        final long start = System.currentTimeMillis();
        String sql = "insert into goods(name) values(?)";
        final PreparedStatement preparedStatement = conn.prepareStatement(sql);
        for (int i = 0; i < 20000; i++) {
            preparedStatement.setString(1,"name"+i);
            preparedStatement.execute();
        }
        JDBCUtil.closeConnection(conn,preparedStatement);
        final long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));
    }

    /*
    批量操作：addBatch(),executeBatch(),clearBatch()
     */
    @Test
    public void test2() throws SQLException, IOException {
        final Connection conn = JDBCUtil.getConnection();
        final long start = System.currentTimeMillis();
        String sql = "insert into goods(name) values(?)";
        final PreparedStatement preparedStatement = conn.prepareStatement(sql);
        System.out.println(preparedStatement.getClass());
        for (int i = 1; i <= 10000000; i++) {
            preparedStatement.setString(1,"name");
            // 积攒攒sql
            preparedStatement.addBatch();
            if (i % 500 == 0) {
                // 批量执行
                preparedStatement.executeBatch();
                // 批量清除
                preparedStatement.clearBatch();
            }
        }
        JDBCUtil.closeConnection(conn,preparedStatement);
        final long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));
    }

    /*
   批量操作：不允许自动提交
    */
    @Test
    public void test3() throws SQLException, IOException {
        final Connection conn = JDBCUtil.getConnection();
        // 临时关闭事务的自动提交
        conn.setAutoCommit(false);
        final long start = System.currentTimeMillis();
        String sql = "insert into goods(name) values(?)";
        final PreparedStatement preparedStatement = conn.prepareStatement(sql);
        for (int i = 1; i <= 10000000; i++) {
            preparedStatement.setString(1,"name");
            // 攒sql
            preparedStatement.addBatch();
            if (i % 500 == 0) {
                // 批量执行
                preparedStatement.executeBatch();
                // 批量清除
                preparedStatement.clearBatch();
            }
        }
        // 正式提交事务，无法回滚
        conn.commit();
        JDBCUtil.closeConnection(conn,preparedStatement);
        final long end = System.currentTimeMillis();
        System.out.println("耗时：" + (end - start));
        // 耗时：9006
    }





}
